The present invention is related to a relational database compiled/stored on a memory structure and adapted by access by application programs executing a query within said database.
The present invention is more precisely related to a system for developing software to be compiled in said computer.
A database takes much of the burden of storing and managing data out of the application.
The advantages of using a database are manifold. The most important are: centralized control of data storage, sharing of data (between applications and users), centralized security, concurrency control, reduction of redundancy, . . .
Database management systems are commercially available for virtually any computer platform. The technology has proven itself and it is cost effective to use a database. The alternative to using a database is using computer files to store the data. The application becomes responsible for each of the database tasks mentioned above. Usually this option is only taken if there is no need for concurrency control, data sharing, security, . . . This is almost only the case in single user (PC) systems.
There are several approaches possible in building a database management system, but the most important is the relational approach. Relational database management systems became commercially available in the late 1970s. Their main advantage over existing database technology was that they simplified the view the user had of the data. A very loose definition of a relational database management system is given as follows: a relational system is a system in which the data are perceived by the user as tables (and nothing but tables); and the operators at the user""s disposal (e.g. for data retrieval) are operators that generate new tables from old. This definition looks at a relational database management system from the user""s point of view. The actual storage format of the data is of course much more complicated. But this shows the main advantage of relational systems: the user only deals with the simplified view and is completely shielded from the lower level implementation structures.
The vendors of relational systems have standardized the way to access a relational database. Practically all databases support SQL (Structured Query Language). SQL is the language with which the user can create new tables from existing ones. SQL is a declarative language: the users specifies what data need to be retrieved and from what tables but not how they need to be retrieved. Since the user has no knowledge of the underlying physical data structures, it is impossible for him to decide how the data should be retrieved. Therefore the system needs to translate each SQL statement into a query path. This specifies how the physical structures are accessed to retrieve the data.
Usually many query paths are possible for one SQL query. Almost all relational systems now have a component called the query optimizer. The query optimizer selects the path that will probably produce the desired result in the shortest time. To speed up the retrieval of data, the database administrator can define indexes. An index is a data structure that stores all values for a particular column in some table and keeps a reference to the rows containing that value. The values are stored in a structure that allows fast retrieval of a particular value. If a user wants to retrieve all the rows containing a specified value, the system can look up the value in the index, retrieve the references to the rows containing that value and use those references to directly retrieve the rows. This will be faster than scanning the whole table and checking each row for the specified value, because the index is smaller, the index is structured so that the relevant value can be retrieved without scanning the whole index. The main performance increase comes from reducing disk accesses. The disk is a mechanical device and is slow as compared to access to internal memory. Indexes are hidden from the user and the results of the query are the same whether the indexes were used or not.
The query optimizer decides which indexes will be used and in what order. To take this decision the query optimizer takes into account the length of the tables, the availability of indexes, the selectivity of the index, . . . It will always try to reduce the amount of rows to retrieve as early as possible in the query. The selection of a query path is done using heuristics so it can not be guaranteed that the optimizer will produce the optimal path. As it will be seen later on, the optimizer often does not have enough information to produce the optimal path. This is often the case for complex queries and then one can aid the optimizer by splitting the query in smaller parts which are easier to optimize.
Problem Definition
If a data model xe2x80x9cshapes one""s view and limits one""s perceptionsxe2x80x9d then those limitations have to be as few as possible and the shaping should not be distorting. This shaping and limiting of the view of the world is in part a preferred effect: it structures one""s world view and puts the focus on that part of the data to be manipulated using a computer. Therefore a data model is subject to three major forces:
the desire to model a defined world of interest as completely as possible,
the structure to impose on this model of the world, and
the limitations of the level of complexity in relation to the hardware and the software.
With time reality changes as do one""s views of this reality. For these reasons data models might have to be changed during the life time of an application. Updates of the data model are costly in terms of human resources. Thus a data model should also be time-resistant.
The model should also allow for any data structure to be entered since a limitation of what can be entered would also limit one""s world view through the model. That way all information can be captured without distortion and different models can be deduced from these raw data. The model is only deduced from the raw data. No part of the model is implied or enforced by the internal data structures since any type of structure can be entered. If the model is only deduction this also makes it time resistant. To change the model one xe2x80x9conlyxe2x80x9d has to change the deductions, not the data representation. Another advantage is that several models can be deduced (and used) at the same time on the same raw data. Since the data are truly xe2x80x9crawxe2x80x9d, that is not distorted to accommodate the structure of the internal data representation, these data can be seen as truths or essences: somebody at a certain point in time has stated that X was true. Entering data is adding a message to a gigantic pool of messages. The world is chaotic and the structure of the chaos to model is in the eye of the beholder. The problem is that the less information in the data representation, the more information needs to be deduced. If there is no structure inherent in the internal data representation this becomes a gigantic task. One trade freedom of storage for complexity of deduction. Current database technology does not allow this freedom of storage.
Relational databases are state of the art but have a much more rigid storage format than described above. In a relational database data are represented in the form of tables. A table has columns and the rows which are called records or tuples. So for each type of message a table is defined to store all the messages of that type. A message becomes a row in that table. Tables have to be defined before data can be stored in them. This limits the freedom: to foresee which types of messages will be needed. Because the structures have to be created beforehand, the users cannot add messages that where not anticipated. Using a query language the programmer can deduce new virtual tables (called xe2x80x9cviewsxe2x80x9d) from other tables. A query is the deduction of a set of tuples (view) from several other sets of tuples (tables and views). A typical database can handle queries that use up to 16 tables, but a query with 16 tables will be far too slow to use in an on-line interactive system. This severely limits the level of complexity of the deductions possible for queries that require fast response times.
It is an option to choose data modelling strategy that captures all raw data. This is done in the form of messages. A message is the content of an electronic form. The messages are in essence statements or essences and have the implied generic form of xe2x80x9cuser X at time Y states that Z is truexe2x80x9d. This implies that the data model is additive: new information is added as a new message. Messages are never updated: a message that is true at a certain point in time will later always have been true at that point in time. A new message might contradict or supersede an old message but the statement the old message stands for, remains true. So updating is conceptually not allowed in this data modelling strategy. There is one reason why updates are allowed here: the user is fallible and might make a (typing) error when adding a message to the system. These errors need to be corrected. The solution is to send another message of the same type to the database and to state that the new message is a correction of the previous one. The old message is only logically deleted, i.e. in the deduction of the present model in most cases a message is ignored whenever there exists another message that corrects it. At the interface level the user can correct the content of the electronic form and with one mouse-click send the new message. The system automatically omits the old version logically and links the new version to the old.
According. to this option the database will grow while query performance degrades. This is a bottleneck related to unlimited database growth. The next bottleneck that will arise is recovery time. The larger the database, the longer it takes for back up or to reload it from tape in case of media failure.
In order to have a clear vision of the main characteristics of the present invention, several definitions are given hereunder.
Conceptual tables are tables of a first set of tables which can be modified by adding another tuple with data to the table.
Non-conceptual tables are tables of a second set of tables which can be modified by whatever action, e.g.:
update of one column of a tuple
addition of another tuple
update/change of one tuple
deletion of tuples
etc.
In the present invention, data can be defined as:
a tuple,
a column,
an element on the cross of a column and a tuple, or
parts thereof.
Database management systems store meta-data, this is data about data. These data are kept in the data dictionary. Typical meta-data are the types (character, integer, real number, . . . ) for each of the columns in a table. Meta-data are used by the system to check for errors.
Redundant should be understood as:
a copy of data,
a xe2x80x9cderivationxe2x80x9d of any data like
an operation (multiplication, division, percentage, combination, verifying the presence of, . . . ) on any data, only using one data point,
an operation (multiplication, division, percentage, combination, verifying the presence of, . . . ) on any data, using several data points (tuples, columns in the same table or in several tables).
An application can be defined as a set of programs that integrate a specific functionality.
A service can be defined as any act that can be asked for or executed by a patient. For instance, a service type can be any act of radiology or nursing.
Finally, a unit (or unit of work) is a unity of acts that are executed in a specific order (the unity is made by the unity of patient, the unity of treatment, the unity of service, . . . ).
The present invention is first related to a relational database compiled/stored on a computer environment and adapted for access by application programs executing a query within said database and compiled/stored on said computer environment comprising:
a first set of tables with first columns and tuples containing first data;
a second set of tables with second columns and tuples containing second data, each of said second data being a redundant representation of at least one of said first data.
A second object of the present invention is related to a method for executing queries within a relational database, comprising the steps of:
selecting tuples in said second set of tables, and
reading out the tuples of said first set of tables corresponding to said selected tuples of said second tables.
A third object of the present invention is related to a database access system compiled on a computer environment, comprising:
a relational database including
a first set of tables with first columns and tuples containing first data,
a second set of tables with second columns and tuples containing second data, each of said second data being a redundant representation of at least one of said first data; said database being adapted for access by application programs executing a query in said database;
meta-data; and
means for optimizing the steps of accessing said database, said means comprising tools for retaining a set of meta-data after first data are inserted in said database, said set of meta-data verifying errors in an insertion of next first data in said database.
A fourth object of the present invention is related to a clinical workstation implementing on a computer environment a representation of a group of processes, operations, services, acts, objects and persons within a hospital comprising:
a relational database having:
a first set of tables with first columns and tuples containing first data, said first data being electronic messages being the contents of electronic forms wherein said first set. of tables is organised as a set of generalized tables according to the different types of said operations;
a second set of tables with second columns and tuples containing second data, each of said second data being a redundant representation of at least one of said first data;
software modules, integrating a medically logical unit of said hospital.
a user interface including a dashboard and integrating said modules; and
a generator stored on said computer environment comprising:
first tools supporting a user interface;
second tools accessing said database;
third tools implementing said electronic messages and linking said database with said user interface whereby said messages are stored as data in said database and whereby said messages can be entered or accessed via said user interface.
A fifth object of the present invention is related to a hospital information system stored on a network of computers an workstations comprising:
a relational database stored on at least one computer of said network comprising:
a first set of tables with first columns and tuples containing first data, said first data being electronic messages being the contents of electronic forms;
a second set of tables with second columns and tuples containing second data, each of said second data representing at least one of said first data;
a generator stored on at least one computer of said network comprising:
first tools supporting a user interface;
second tools accessing said database;
third tools implementing said electronic messages and linking said database with said user interface whereby said messages are stored as data in said database and whereby said messages can be entered or accessed via said user interface; and
at least one clinical workstation comprising said user interface and modules allowing for entry of essences consisting of the group of processes, operations, services, acts and objects related to a patient""s medical file within the hospital.
More particularly, the present invention will be described in relation of a clinical workstation and its integration within an existing administrative system to form an hospital information system.
It was decided not to build each application separately but to construct a basic software layer (the generator) on top of which applications were built. The generator consists of three main modules, one to handle the graphical user interface, one to handle database access and one to incorporate the basic user interface paradigm. This last module functionally ties the two other modules together.
According to a preferred embodiment of the present invention, the generator uses Sybase as database, Prolog as application language, MacWorkstation and later X-windows for the graphical user interface.
The purpose of the generator is:
to shield the applications from the underlying software components. This diminishes the dependence on them;
to provide a higher level functional interface for these components. This allows faster application development since it allows to use larger building blocks;
support for a basic user interface and data modelling paradigm. Including this in the generator causes all applications to share these common features. This makes the system more user friendly and easier to master.
A new data modelling strategy is disclosed hereunder. This strategy discerns two types of data tables:
conceptual tables are known to the application and its users. They model the world of interest.
non-conceptual tables do not exist from a user point of view. They contain redundant data and embody an alternative data model.
Both performance and design benefit from separating tables in two classes:
performance is enhanced by giving the non-conceptual tables a data model that facilitates those queries that are not performant when run on the conceptual tables. Usually, only the minimal information necessary to speed up those queries are stored;
design is facilitated because less performance compromises need to be made in building the conceptual data model. Using this technique the design for expressivity is separated from the design for performance.
For the preferred embodiment of the present system application according to the present invention (called conceptual model of system 9), several specifications were developed:
the data model is deletionless; that is, data are never physically deleted, only logically.
the data model is additive. This means that updates are conceptually not allowed in the model. Data entry is seen as true statements (messages) from a user. The data model is a set of tables to hold structured messages. Normally a message does not need to be corrected unless an error was made. Since the model is deletionless both versions of the message are kept when it is corrected.
The present system conceptual model according to the present invention consists of three different levels:
the physical level hosts all data whether logically deleted or not;
the conceptual level is defined on top of the physical level and shows the current version of the messages. This is the level the generator operates on;
the application level defines the relationships between the messages.
These four data models (the three conceptual levels and the non-conceptual tables) form a meta-model. The use of the meta-model facilitates reasoning over the global data model: the generator and the application operate on different levels of the conceptual model and performance problems are solved in the non-conceptual tables. Each model has a specific function in the whole of the application.
During the development of the system, the electronic form was chosen as the basic interface object. The user interacts with the system by sending and viewing electronic forms. This electronic mail-like metaphor matches well with a deletionless and additive data model. The basic form operations are implemented in the generator and do not need to be implemented in each application.
As an essential element in the user interface the dashboard was developed. The dashboard is the main instrument to navigate between different functions of the electronic medical record for a patient. It was developed for the first present system application, and was repeated in all subsequent applications. In a way the dashboard bundles the data for a patient and creates a virtual electronic medical file.
To solve the problem of implementing the diversity inherent to order entry, a generic tool is developed that it forms the basis for all order entry. Several subproblems were solved:
an interface paradigm consisting of a selector, an editor and a collector was developed. These three items interact in a standard way in all order entry applications. Rule bases are used to steer the behaviour for specific functionality;
the logical data structures used to represent orders in this system needed to be translated to the more rigid relational format. All structures are stored in a set of four tables by translating them to a canonical format using a rule base. Thus the diversity of order entry can be handled without creating an unmaintainable number of database objects.
The generator which is disclosed here supports the user interface metaphor and data modelling strategy. It is the basis for all three clinical applications implemented. This demonstrates the general applicability of the tools and the metaphors used.